using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.Data.Extensions;
using Devart.Data.PostgreSql;

namespace MvcCms.Data.Code
{
    public class EntityAccountRepository : IEntityAccountRepository
    {
        #region Head

        private readonly MvcCmsEntities _entities;
        private readonly IEntitiesProvider _entitiesProvider;

        public EntityAccountRepository(IEntitiesProvider entitiesProvider)
        {
            _entities = entitiesProvider.GetEntityContext();
            _entitiesProvider = entitiesProvider;
        }

        #endregion
        #region Materialize<T>
        private static readonly Materializer<User> UserMaterializer =
            new Materializer<User>(r =>
              new User
              {
                  UserID = r.Field<Guid>("userid"),
                  Name = r.Field<String>("name"),
                  CreationDate = r.Field<DateTime?>("creationdate"),
                  UserName = r.Field<String>("username"),
                  Email = r.Field<String>("email"),
                  Password = r.Field<String>("password"),
                  PasswordQuestion = r.Field<String>("passwordquestion"),
                  PasswordAnswer = r.Field<String>("passwordanswer"),
                  IsApproved = r.Field<bool>("isapproved"),
                  LastActivityDate = r.Field<DateTime?>("lastactivitydate"),
                  LastLoginDate = r.Field<DateTime?>("lastlogindate"),
                  LastPasswordChangedDate = r.Field<DateTime?>("lastpasswordchangeddate"),
                  IsOnline = r.Field<bool?>("isonline"),
                  IsLockedOut = r.Field<bool?>("islockedout"),
                  LastLockedOutDate = r.Field<DateTime?>("lastlockedoutdate"),
                  FailedPasswordAnswerAttemptCount = r.Field<int?>("failedpasswordattemptcount"),
                  FailedPasswordAttemptWindowStart = r.Field<DateTime?>("failedpasswordattemptwindowstart"),
                  FailedPasswordAttemptCount = r.Field<int?>("failedpasswordanswerattemptcount"),
                  FailedPasswordAnswerAttemptWindowStart = r.Field<DateTime?>("failedpasswordanswerattemptwindowstart"),
                  LastModified = r.Field<DateTime?>("lastmodified"),
                  Comment = r.Field<string>("comment"),
                  PortalID = r.Field<int>("portalid"),
                  OpenId = r.Field<string>("openid"),
                  FireFileKey = r.Field<string>("firefilekey")
              });

        #endregion

        #region IAccountRepository Members

        public IEnumerable<DTOContributor> ListContributors(Portal portal)
        {
            return (from c in _entities.DTOContributorSet
                    where c.PortalID == portal.PortalID
                    orderby c.UserName
                    select c).ToList();
        }
        public User GetUser(Guid userId)
        {
            return (from s in _entities.UserSet
                        .Include("Portal")
                    where s.UserID == userId
                    select s).FirstOrDefault();
        }

        public User GetUser(string userName)
        {
            return (from s in _entities.UserSet
                        .Include("Portal")
                    where s.UserName.ToLower() == userName.ToLower() || s.Email.ToLower() == userName.ToLower()
                    select s).FirstOrDefault();
        }
        public User GetUserByOpenId(string openId, int portalId)
        {
            return (from s in _entities.UserSet
                        .Include("Portal")
                    where s.OpenId == openId && s.Portal.PortalID == portalId
                    select s).FirstOrDefault();
        }
        public User GetUser_MPCtxt(Guid userId)
        {
            //the membership provider needs its own datacontext
            using (var db = new MvcCmsEntities())
            {
                return (from s in db.UserSet
                            .Include("Portal")
                        where s.UserID == userId
                        select s).FirstOrDefault();
            }
        }

        public User GetUser_MPCtxt(string userName)
        {
            //the membership provider needs its own datacontext
            using (var db = new MvcCmsEntities())
            {
                return (from s in db.UserSet
                            .Include("Portal")
                        where s.UserName.ToLower() == userName.ToLower() || s.Email.ToLower() == userName.ToLower()
                        select s).FirstOrDefault();
            }
        }

        public User CreateUser(User userToCreate)
        {
            DbCommand command = _entities.CreateStoreCommand("insert_user",
                 CommandType.StoredProcedure,
                 new PgSqlParameter("_userid", userToCreate.UserID),
                 new PgSqlParameter("_username", userToCreate.UserName),
                 new PgSqlParameter("_email", userToCreate.Email),
                 new PgSqlParameter("_portalid", userToCreate.Portal.PortalID),
                 new PgSqlParameter("_isapproved", userToCreate.IsApproved),
                 new PgSqlParameter("_isonline", userToCreate.IsOnline),
                 new PgSqlParameter("_islockedout", userToCreate.IsLockedOut),
                 new PgSqlParameter("_lastactivitydate", userToCreate.LastActivityDate),
                 new PgSqlParameter("_lastlogindate", userToCreate.LastLoginDate),
                 new PgSqlParameter("_lastpasswordchangeddate", userToCreate.LastPasswordChangedDate),
                 new PgSqlParameter("_lastlockedoutdate", userToCreate.LastLockedOutDate),
                 new PgSqlParameter("_failedpasswordattemptcount", userToCreate.FailedPasswordAttemptCount),
                 new PgSqlParameter("_failedpasswordattemptwindowstart", userToCreate.FailedPasswordAttemptWindowStart),
                 new PgSqlParameter("_failedpasswordanswerattemptcount", userToCreate.FailedPasswordAnswerAttemptCount),
                 new PgSqlParameter("_failedpasswordanswerattemptwindowstart", userToCreate.FailedPasswordAnswerAttemptWindowStart),
                 new PgSqlParameter("_lastmodified", userToCreate.LastModified),
                 new PgSqlParameter("_comment", userToCreate.Comment),
                 new PgSqlParameter("_password", userToCreate.Password),
                 new PgSqlParameter("_passwordquestion", userToCreate.PasswordQuestion),
                 new PgSqlParameter("_passwordanswer", userToCreate.PasswordAnswer)
                );
            using (command.Connection.CreateConnectionScope())
            {
                command.ExecuteReader();
                return userToCreate;
            }
        }

        public User GetUserByEmail(string email)
        {
            return (from s in _entities.UserSet
                        .Include("Portal")
                    where s.Email.ToLower() == email.ToLower()
                    select s).FirstOrDefault();
        }

        public int GetNumberOfUsersOnline(int timewindow, Portal portal)
        {
            var onlineSpan = new TimeSpan(0, timewindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            return (from u in _entities.UserSet
                    where u.LastActivityDate > compareTime &&
                          u.Portal.PortalID == portal.PortalID
                    select u).Distinct().Count();
        }

        public IEnumerable<User> ListUsers(int pageIndex, int pageSize, Portal portal)
        {
            return ((from c in _entities.UserSet
                     where c.Portal.PortalID == portal.PortalID
                     orderby c.UserName
                     select c).Skip(pageIndex * pageSize).Take(pageSize)).ToList();
        }

        public IEnumerable<User> ListUsers(Portal portal)
        {
            return (from c in _entities.UserSet
                    where c.Portal.PortalID == portal.PortalID
                    orderby c.UserName
                    select c).ToList();
        }
        public IEnumerable<User> ListUsersWithProfile(Portal portal)
        {
            return (from c in _entities.UserSet
                    .Include("Profile")
                    where c.Portal.PortalID == portal.PortalID
                    select c);
        }
        public IEnumerable<User> SearchUsers(string columnName, string searchTerm, Portal portal)
        {
            var searchLikeTerm = "%" + searchTerm + "%";
            var username = columnName == "UserName" ? searchLikeTerm : null;
            var email = columnName == "Email" ? searchLikeTerm : null;
            var creationDate = columnName == "CreationDate" ? searchTerm : null;
            var lastActivityDate = columnName == "LastActivityDate" ? searchTerm : null;
            var isApproved = columnName == "IsApproved" ? searchTerm : null;
            bool? isApprovedVal = null;
            DateTime? lastActivityDateVal = null;
            DateTime? creationDateVal = null;
            var portalId = portal.PortalID;
            if (isApproved == "false") { isApprovedVal = true; }
            if (!string.IsNullOrEmpty(creationDate)) { creationDateVal = DateTime.Parse(creationDate); }
            if (!string.IsNullOrEmpty(lastActivityDate)) { lastActivityDateVal = DateTime.Parse(lastActivityDate); }

            DbCommand command = _entities.CreateStoreCommand("Search_Users",
                    CommandType.StoredProcedure,
                    new PgSqlParameter("_UserName", username),
                    new PgSqlParameter("_Email", email),
                    new PgSqlParameter("_CreationDate", creationDateVal),
                    new PgSqlParameter("_LastActivityDate", lastActivityDateVal),
                    new PgSqlParameter("_IsApproved", isApprovedVal),
                    new PgSqlParameter("_PortalID", portalId)
            );

            using (command.Connection.CreateConnectionScope())
            {
                return UserMaterializer.Materialize(command);
            }
        }
        public void DeleteUser(string userName)
        {
            User originalUser = (from s in _entities.UserSet.Include("Profile")
                                 where s.UserName.ToLower() == userName.ToLower()
                                 select s).FirstOrDefault();

            Profile profiletodel = GetProfile(originalUser.UserID);
            if (profiletodel != null)
            {
                _entities.DeleteObject(profiletodel);
            }
            _entities.DeleteObject(originalUser);
            _entities.SaveChanges();
        }
        public User EditUserEntity(User userToEdit)
        {
            // Save changes
            _entities.UserSet.ApplyCurrentValues(userToEdit);
            _entities.SaveChanges();
            return userToEdit;
        }
        public User EditUser(User userToEdit)
        {
            DbCommand command = _entities.CreateStoreCommand("update_user",
                 CommandType.StoredProcedure,
                 new PgSqlParameter("_UserID", userToEdit.UserID),
                 new PgSqlParameter("_Email", userToEdit.Email),
                 new PgSqlParameter("_PortalID", userToEdit.Portal.PortalID),
                 new PgSqlParameter("_IsApproved", userToEdit.IsApproved),
                 new PgSqlParameter("_IsOnline", userToEdit.IsOnline),
                 new PgSqlParameter("_IsLockedOut", userToEdit.IsLockedOut),
                 new PgSqlParameter("_LastActivityDate", userToEdit.LastActivityDate),
                 new PgSqlParameter("_LastLoginDate", userToEdit.LastLoginDate),
                 new PgSqlParameter("_LastPasswordChangedDate", userToEdit.LastPasswordChangedDate),
                 new PgSqlParameter("_LastLockedOutDate", userToEdit.LastLockedOutDate),
                 new PgSqlParameter("_FailedPasswordAttemptCount", userToEdit.FailedPasswordAttemptCount),
                 new PgSqlParameter("_FailedPasswordAttemptWindowStart", userToEdit.FailedPasswordAttemptWindowStart),
                 new PgSqlParameter("_FailedPasswordAnswerAttemptCount", userToEdit.FailedPasswordAnswerAttemptCount),
                 new PgSqlParameter("_FailedPasswordAnswerAttemptWindowStart", userToEdit.FailedPasswordAnswerAttemptWindowStart),
                 new PgSqlParameter("_LastModified", userToEdit.LastModified),
                 new PgSqlParameter("_Comment", userToEdit.Comment),                                  
                 new PgSqlParameter("_Password", userToEdit.Password),
                 new PgSqlParameter("_PasswordQuestion", userToEdit.PasswordQuestion),
                 new PgSqlParameter("_PasswordAnswer", userToEdit.PasswordAnswer),
                 new PgSqlParameter("_FireFileKey", userToEdit.FireFileKey),
                 new PgSqlParameter("_OpenId", userToEdit.OpenId)
                );

            using (_entities.Connection.CreateConnectionScope())
            {
                command.ExecuteNonQuery();
                return userToEdit;
            }
        }

        #endregion

        #region Profile methods

        public Profile GetProfile(Guid userKey)
        {
            return (from s in _entities.ProfileSet
                        .Include("Language")
                        .Include("StateProvince")
                        .Include("User")
                    where s.User.UserID == userKey
                    select s).FirstOrDefault();
        }

        public Profile SetProfileData(Profile profileToEdit)
        {
            Profile orig = GetProfile(profileToEdit.User.UserID);
            if (orig == null)
            {
                _entities.ProfileSet.AddObject(profileToEdit);
                _entities.SaveChanges();
            }
            else
            {
                _entities.ProfileSet.ApplyCurrentValues(profileToEdit);
                _entities.SaveChanges();
            }
            return profileToEdit;
        }

        public Profile GetProfile(int profileId)
        {
            return (from s in _entities.ProfileSet
                        .Include("Language")
                        .Include("User")
                    where s.ProfileID == profileId
                    select s).FirstOrDefault();
        }

        #endregion

        #region Role Methods

        public IEnumerable<DTOUserRole> ListUsersInRole(string roleName, int portalId)
        {
            return (from p in _entities.DTOUserRoleSet
                    where p.RoleName.ToLower() == roleName.ToLower() && p.PortalID == portalId
                    select p);
        }

        public IEnumerable<User> ListUsersNotInRole(string roleName, int portalId)
        {
            DbCommand command = _entities.CreateStoreCommand("get_users_not_in_role",
                 CommandType.StoredProcedure,
                 new PgSqlParameter("_role_name", roleName),
                 new PgSqlParameter("_portalid", portalId)
            );
            using (command.Connection.CreateConnectionScope())
            {
                return UserMaterializer.Materialize(command);
            }

        }
        public Role GetRole(string loweredRoleName)
        {
            return (from s in _entities.RoleSet
                    where s.LoweredRoleName == loweredRoleName.ToLower()
                    select s).FirstOrDefault();
        }
        public Role GetRole(Guid roleId)
        {
            return (from s in _entities.RoleSet
                    where s.RoleId == roleId
                    select s).FirstOrDefault();
        }
        #endregion
    }
}